﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Caching;

namespace MvcApplication1.Models
{
    public class DataToSearch
    {
        //#region 原始版本，使用缓存
        //private string sqlconnstr;
        //private static DataSet itemName;    //static使得每次对象实例运行时ds可以保留前面实例中创建的ds引用，保存商品名称
        //private static DataSet itemBrand;   //保存商品品牌
        //private static DataSet itemKeyword;
        //public DataToSearch(string sqlconnstr)
        //{
        //    this.sqlconnstr = sqlconnstr;
        //}

        //#region cache与dataset注释
        ////代码中将datatable而不是dataset添加到cache中，代码运行时会有三种情况：启动时dataset为null，cache中也没有datatable；
        ////运行后dataset不为null，cache中也有表；当数据库变化时，dataset不为null，但cache中表为null。
        //#endregion
        //public bool Init()  //返回true成功，false失败
        //{
        //    SqlConnection sqlconn = new SqlConnection(this.sqlconnstr);
        //    //打开连接
        //    try
        //    {
        //        sqlconn.Open();
        //    }
        //    catch
        //    {
        //        return false;
        //    }
        //    finally
        //    {
        //        sqlconn.Close();
        //    }
        //    SqlDataAdapter adapter = new SqlDataAdapter();
        //    adapter.SelectCommand = new SqlCommand("select [ID] from [dbo].[brand]", sqlconn);
        //    HttpContext context = HttpContext.Current;
        //    if (context.Cache["brandID"] == null)
        //    {
        //        if (itemBrand == null) itemBrand = new DataSet();
        //        SqlCacheDependency brandCD = new SqlCacheDependency(adapter.SelectCommand);
        //        if (itemBrand.Tables["brandID"] != null) itemBrand.Tables.Remove("brandID");
        //        try
        //        {
        //            adapter.Fill(itemBrand, "brandID");//获取品牌列表
        //        }
        //        catch
        //        {
        //            return false;
        //        }
        //        context.Cache.Insert("brandID", itemBrand.Tables["brandID"], brandCD);
        //        //context.Cache.Add("brandID", ds.Tables["brandID"], new SqlCacheDependency(adapter.SelectCommand), Cache.NoAbsoluteExpiration, new TimeSpan(1, 0, 0), CacheItemPriority.Normal, new CacheItemRemovedCallback(RemoveCallback));
        //    }
        //    else
        //    {
        //        itemBrand = ((DataTable)context.Cache["brandID"]).DataSet;  //防止网站意外关闭后，itemBrand为null而无法获取缓存中dataTable所属dataSet的问题
        //    }

        //    if (context.Cache["itemNameStub"] == null)  ////防止网站意外关闭后，itemName为null而无法获取缓存中dataTable所属dataSet的问题
        //    {
        //        if (itemName == null) itemName = new DataSet();
        //        itemName.Tables.Add("itemNameStub");
        //        context.Cache.Insert("itemNameStub", itemName.Tables["itemNameStub"]);
        //    }
        //    else
        //    {
        //        itemName = ((DataTable)context.Cache["itemNameStub"]).DataSet;
        //    }
        //    if (context.Cache["itemKeywordStub"] == null)  //
        //    {
        //        if (itemKeyword == null) itemKeyword = new DataSet();
        //        itemKeyword.Tables.Add("itemKeywordStub");
        //        context.Cache.Insert("itemKeywordStub", itemKeyword.Tables["itemKeywordStub"]);
        //    }
        //    else
        //    {
        //        itemKeyword = ((DataTable)context.Cache["itemKeywordStub"]).DataSet;
        //    }
        //    foreach (DataRow row in itemBrand.Tables["brandID"].Rows)
        //    {
        //        if (InitItemName(itemName, adapter, sqlconn, context, row) == false) return false;//获取商品表，每个品牌单独一张表，表名为品牌ID，分别存储到缓存中
        //        if (InitKeyword(itemKeyword, adapter, sqlconn, context, row) == false) return false;

        //    }

        //    return true;
        //}
        ////返回itemname dataset给searcher对象
        //public DataSet GetItemName()
        //{
        //    if (DataToSearch.itemName != null)
        //    {
        //        return DataToSearch.itemName;
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}

        ////返回itemkeyword dataset给searcher对象
        //public DataSet GetItemKeyword()
        //{
        //    if (DataToSearch.itemKeyword != null)
        //    {
        //        return DataToSearch.itemKeyword;
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}

        
        ////public void UpdateItem(ItemEdit item)
        ////{
        ////    string brand;
        ////    HttpContext context = HttpContext.Current;
        ////    SqlConnection conn = new SqlConnection(sqlconnstr);
        ////    SqlCommand cmd = new SqlCommand("select brandID from [cj].[dbo].[item] where item.ID=@itemID", conn);
        ////    cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value=item.GetItemID();
        ////    try
        ////    {
        ////        conn.Open();
        ////        brand = cmd.ExecuteScalar().ToString();
        ////    }
        ////    catch (SqlException e)
        ////    {
        ////        throw e;
        ////    }
        ////    finally
        ////    {
        ////        conn.Close();
        ////    }
        ////    itemName.Tables.Remove(brand);
        ////    itemName.Tables.Add((DataTable)context.Cache[brand]);
        ////    itemName.Tables[itemName.Tables.Count - 1].TableName = brand;
           
        ////}

        ////从数据库中读取商品信息并存入缓存
        ////itemName存放各品牌商品表
        //private bool InitItemName(DataSet itemName, SqlDataAdapter adapter, SqlConnection sqlconn, HttpContext context, DataRow row)
        //{
        //    if (context.Cache[row[0].ToString()] == null)   //查找缓存中是否有key为该品牌ID的项(该品牌商品名称表）
        //    {
        //        adapter.SelectCommand = new SqlCommand("select item.ID,item.itemname,item.itemSN,item.addTime,item.image,item.linkaddr,item.price,item.intro,brand.brandname from [dbo].[item] inner join [dbo].[brand] on item.brandID=brand.ID where item.brandID=@brandID", sqlconn);
        //        //adapter.SelectCommand = new SqlCommand("select item.ID,item.itemname,item.itemSN,item.addTime,item.image,item.linkaddr,item.price,item.intro from [dbo].[item] where brandID=@brandID", sqlconn);
        //        adapter.SelectCommand.Parameters.Add("@brandID", SqlDbType.BigInt);
        //        adapter.SelectCommand.Parameters["@brandID"].Value = row[0];
        //        SqlCacheDependency itemtabledepend = new SqlCacheDependency(adapter.SelectCommand);        //注意：sql缓存依赖一定要在sql命令执行之前建立，因为命令执行时该依赖对象被送到数据库处理
        //        if (itemName.Tables[row[0].ToString()] != null) itemName.Tables.Remove(row[0].ToString());  //原数据虽然在cache中删除但没有在dataset中删除，需要手动删除
        //        try
        //        {
        //            adapter.Fill(itemName, row[0].ToString());
        //            DataColumn[] key = new DataColumn[1];
        //            key[0] = itemName.Tables[row[0].ToString()].Columns["ID"];
        //            itemName.Tables[row[0].ToString()].PrimaryKey = key;
        //        }
        //        catch
        //        {
        //            return false;
        //        }
        //        context.Cache.Insert(row[0].ToString(), itemName.Tables[row[0].ToString()], itemtabledepend);
        //        //不需回调函数，因为sqlcachedependency会自动删除旧数据和查询新数据
        //        //context.Cache.Add(row[0].ToString(), ds.Tables[row[0].ToString()], itemtabledepend, Cache.NoAbsoluteExpiration, new TimeSpan(1, 0, 0), CacheItemPriority.Normal, new CacheItemRemovedCallback(RemoveCallback));
        //    }
        //    else
        //    {
        //        if (itemName.Tables[row[0].ToString()] == null)
        //        {
        //            itemName.Tables.Add((DataTable)context.Cache[row[0].ToString()]);
        //            itemName.Tables[itemName.Tables.Count - 1].TableName = row[0].ToString();
        //        }
        //    }
        //    return true;
        //}
        //private bool InitKeyword(DataSet itemKeyword, SqlDataAdapter adapter, SqlConnection sqlconn, HttpContext context, DataRow row)
        //{
        //    if (context.Cache[row[0].ToString() + "key"] == null)//查找缓存中是否有key为该品牌ID加“key”的项（该品牌的关键字表）
        //    {
        //        adapter.SelectCommand = new SqlCommand("select item.ID,itemKeyword.keywordSN,itemKeyword.keyword from item inner join itemKeyword on item.ID=itemKeyword.itemID where item.brandID=@brandID", sqlconn);
        //        adapter.SelectCommand.Parameters.Add("@brandID", SqlDbType.BigInt);
        //        adapter.SelectCommand.Parameters["@brandID"].Value = row[0];
        //        SqlCacheDependency keywordtabledepend = new SqlCacheDependency(adapter.SelectCommand);
        //        if (itemKeyword.Tables[row[0].ToString()] != null) itemKeyword.Tables.Remove(row[0].ToString());
        //        try
        //        {
        //            adapter.Fill(itemKeyword, row[0].ToString());
        //        }
        //        catch
        //        {
        //            return false;
        //        }
        //        context.Cache.Insert(row[0].ToString() + "key", itemKeyword.Tables[row[0].ToString()], keywordtabledepend);
        //    }
        //    else
        //    {
        //        if (itemKeyword.Tables[row[0].ToString()] == null)
        //        {
        //            itemKeyword.Tables.Add((DataTable)context.Cache[row[0].ToString()]);
        //            itemKeyword.Tables[itemKeyword.Tables.Count - 1].TableName = row[0].ToString();
        //        }
        //    }
        //    return true;
        //}
        //#endregion

        #region 第二版 
        
        private string sqlconnstr;
        private DataSet itemName;    
        private DataSet itemBrand;   //保存商品品牌
        private DataSet itemKeyword;
        public DataToSearch(string sqlconnstr)
        {
            this.sqlconnstr = sqlconnstr;
        }

        #region cache与dataset注释
        //代码中将datatable而不是dataset添加到cache中，代码运行时会有三种情况：启动时dataset为null，cache中也没有datatable；
        //运行后dataset不为null，cache中也有表；当数据库变化时，dataset不为null，但cache中表为null。
        #endregion
        public bool Init()  //返回true成功，false失败
        {
            SqlConnection sqlconn = new SqlConnection(this.sqlconnstr);
            //打开连接
            try
            {
                sqlconn.Open();
            }
            catch
            {
                return false;
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand("select [dbo].[brand].[ID] from [dbo].[brand] inner join [dbo].[user] on [dbo].[brand].[userID]=[dbo].[user].[ID] where [user].[isapproved]=1", sqlconn);
            
            itemBrand = new DataSet();
            try
            {
                adapter.Fill(itemBrand, "brandID");//获取品牌列表
            }
            catch
            {
                return false;
            }

            itemName = new DataSet();
            //itemName.Tables.Add("itemNameStub");
          
            itemKeyword = new DataSet();
            //itemKeyword.Tables.Add("itemKeywordStub");
           
            
            foreach (DataRow row in itemBrand.Tables["brandID"].Rows)
            {
                if (InitItemName(itemName, adapter, sqlconn, row) == false) return false;//获取商品表，每个品牌单独一张表，表名为品牌ID，分别存储到缓存中
                if (InitKeyword(itemKeyword, adapter, sqlconn, row) == false) return false;

            }
            sqlconn.Close();
            return true;
        }
        //返回itemname dataset给searcher对象
        public DataSet GetItemName()
        {
            if (itemName != null)
            {
                return itemName;
            }
            else
            {
                return null;
            }
        }

        //返回itemkeyword dataset给searcher对象
        public DataSet GetItemKeyword()
        {
            if (itemKeyword != null)
            {
                return itemKeyword;
            }
            else
            {
                return null;
            }
        }


        public void UpdateItem(ItemEdit item)
        {
            string brand;
            SqlConnection conn = new SqlConnection(sqlconnstr);
            SqlCommand cmd = new SqlCommand("select brandID from [dbo].[item] where item.ID=@itemID", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value=item.GetItemID();
            try
            {
                conn.Open();
                brand = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            DataTable table = itemName.Tables[brand];
            DataRow[] rows = table.Select("ID=" + item.GetItemID().ToString());
            rows[0][1] = item.GetItemName();
            rows[0][3] = item.GetAddTime();
            rows[0][4] = item.GetImage();
            rows[0][5] = item.GetLinkAddr();
            rows[0][6] = item.GetPrice();
            rows[0][7] = item.GetIntro();
            table.AcceptChanges();
        }

        public void AddItem(ItemEdit item)
        {
            string brand;
            SqlConnection conn = new SqlConnection(sqlconnstr);
            SqlCommand cmd = new SqlCommand("select brandID from [dbo].[item] where item.ID=@itemID", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value = item.GetItemID();
            try
            {
                conn.Open();
                brand = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            DataTable table = itemName.Tables[brand];
            DataRow row = table.NewRow();
            row[0] = item.GetItemID();
            row[1] = item.GetItemName();
            row[2] = item.GetItemSN();
            row[3] = item.GetAddTime();
            row[4] = item.GetImage();
            row[5] = item.GetLinkAddr();
            row[6] = item.GetPrice();
            row[7] = item.GetIntro();
            table.Rows.Add(row);
            table.AcceptChanges();
            AddKeyword(item);
        }

        public void DelItem(Int64[] itemids)
        {
            string brand;
            SqlConnection conn = new SqlConnection(sqlconnstr);
            SqlCommand cmd = new SqlCommand("select brandID from [dbo].[item] where item.ID=@itemID", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value = itemids[0];
            try
            {
                conn.Open();
                brand = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            DataTable table = itemName.Tables[brand];
            DataRow[] rows;
            foreach (Int64 itemid in itemids)
            {
                rows = table.Select("ID=" + itemid.ToString());
                table.Rows.Remove(rows[0]);
            }
            table.AcceptChanges();
            DelKeyword(itemids);
        }

        public void UpdateKeyword(ItemEdit item)
        {
            string brand;
            SqlConnection conn = new SqlConnection(sqlconnstr);
            SqlCommand cmd = new SqlCommand("select brandID from [dbo].[item] where item.ID=@itemID", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value = item.GetItemID();
            try
            {
                conn.Open();
                brand = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            DataTable table = itemKeyword.Tables[brand];

            DataRow[] rows = table.Select("ID=" + item.GetItemID().ToString());
            foreach (DataRow row in rows)
            {
                row.Delete();
            }
            KeywordItem[] keywordlist = item.GetKeywordList();
            if (keywordlist != null)
            {
                for (int i = 0; i < keywordlist.Length; i++)
                {

                    table.Rows.Add(new object[3] { item.GetItemID(), i + 1, keywordlist[i].Keyword });

                }
            }
            table.AcceptChanges();
        }

        public void AddBrand(string username)
        {
            SqlConnection sqlconn = new SqlConnection(this.sqlconnstr);
            //打开连接
            try
            {
                sqlconn.Open();
            }
            catch(SqlException e)
            {
                throw e;
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand("select brand.ID from [dbo].[brand] inner join [dbo].[user] on [user].[ID]=[brand].[userID] where [user].username=@username", sqlconn);
            adapter.SelectCommand.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
            DataTable table =new DataTable();
            try
            {
                adapter.Fill(table);
            }
            catch (SqlException e)
            {
                throw e;
            }
            InitItemName(itemName, adapter, sqlconn, table.Rows[0]);
            InitKeyword(itemKeyword, adapter, sqlconn, table.Rows[0]);
            sqlconn.Close();
        }

        public void DelBrand(string username)
        {
            SqlConnection sqlconn = new SqlConnection(this.sqlconnstr);
            //打开连接
            try
            {
                sqlconn.Open();
            }
            catch (SqlException e)
            {
                throw e;
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand("select brand.ID from [dbo].[brand] inner join [dbo].[user] on [user].[ID]=[brand].[userID] where [user].username=@username", sqlconn);
            adapter.SelectCommand.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
            DataTable table = new DataTable();
            try
            {
                adapter.Fill(table);
            }
            catch (SqlException e)
            {
                throw e;
            }
            if (itemName.Tables[table.Rows[0].ItemArray[0].ToString()] != null)
                itemName.Tables.Remove(table.Rows[0].ItemArray[0].ToString());
            if (itemKeyword.Tables[table.Rows[0].ItemArray[0].ToString()] != null)
                itemKeyword.Tables.Remove(table.Rows[0].ItemArray[0].ToString());
        }
        //当用户更新品牌的brandtitle时，更新内存中的搜索数据集
        public void UpdateBrand(string username)
        {
            DelBrand(username);
            AddBrand(username);
        }

        public void AddKeyword(ItemEdit item)
        {
            string brand;
            SqlConnection conn = new SqlConnection(sqlconnstr);
            SqlCommand cmd = new SqlCommand("select brandID from [dbo].[item] where item.ID=@itemID", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value = item.GetItemID();
            try
            {
                conn.Open();
                brand = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            DataTable table = itemKeyword.Tables[brand];
            KeywordItem[] keywordlist = item.GetKeywordList();
            if (keywordlist != null)
            {
                for (int i = 0; i < keywordlist.Length; i++)
                {

                    table.Rows.Add(new object[3] { item.GetItemID(), i + 1, keywordlist[i].Keyword });

                }
            }
            table.AcceptChanges();
        }

        public void DelKeyword(Int64[] itemids)
        {
            string brand;
            SqlConnection conn = new SqlConnection(sqlconnstr);
            SqlCommand cmd = new SqlCommand("select brandID from [dbo].[item] where item.ID=@itemID", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Value = itemids[0];
            try
            {
                conn.Open();
                brand = cmd.ExecuteScalar().ToString();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            DataTable table = itemKeyword.Tables[brand];
            foreach (Int64 itemid in itemids)
            {
                DataRow[] rows = table.Select("ID=" + itemid.ToString());
                foreach (DataRow row in rows)
                    table.Rows.Remove(row);
            }
            table.AcceptChanges();
        }

        //从数据库中读取商品信息并存入缓存
        //itemName存放各品牌商品表
        private bool InitItemName(DataSet itemName, SqlDataAdapter adapter, SqlConnection sqlconn, DataRow row)
        {
            if (itemName.Tables[row[0].ToString()] != null) return true;
            adapter.SelectCommand = new SqlCommand("select item.ID,item.itemname,item.itemSN,item.addTime,item.image,item.linkaddr,item.price,item.intro,brand.brandTitle from [dbo].[item] inner join [dbo].[brand] on item.brandID=brand.ID where item.brandID=@brandID", sqlconn);
            //adapter.SelectCommand = new SqlCommand("select item.ID,item.itemname,item.itemSN,item.addTime,item.image,item.linkaddr,item.price,item.intro from [dbo].[item] where brandID=@brandID", sqlconn);
            adapter.SelectCommand.Parameters.Add("@brandID", SqlDbType.BigInt);
            adapter.SelectCommand.Parameters["@brandID"].Value = row[0];
            try
            {
                adapter.Fill(itemName, row[0].ToString());
                //DataColumn[] key = new DataColumn[1];
                //key[0] = itemName.Tables[row[0].ToString()].Columns["ID"];
                //itemName.Tables[row[0].ToString()].PrimaryKey = key;
            }
            catch
            {
                return false;
            }
            SqlCommand cmd = new SqlCommand("select brandname from [dbo].[brand] where ID=@brandID", sqlconn);
            cmd.Parameters.Add("@brandID", SqlDbType.BigInt).Value = row[0];
            string brandname;
            try
            {
                brandname = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                return false;
            }
            itemName.Tables[row[0].ToString()].Columns[8].DefaultValue = brandname;
            //itemName.Tables[row[0].ToString()].Columns[0].AutoIncrement = true;
            return true;
        }
        private bool InitKeyword(DataSet itemKeyword, SqlDataAdapter adapter, SqlConnection sqlconn, DataRow row)
        {
            if (itemKeyword.Tables[row[0].ToString()] != null) return true;
            adapter.SelectCommand = new SqlCommand("select item.ID,itemKeyword.keywordSN,itemKeyword.keyword from item inner join itemKeyword on item.ID=itemKeyword.itemID where item.brandID=@brandID", sqlconn);
            adapter.SelectCommand.Parameters.Add("@brandID", SqlDbType.BigInt);
            adapter.SelectCommand.Parameters["@brandID"].Value = row[0];
            //if (itemKeyword.Tables[row[0].ToString()] != null) itemKeyword.Tables.Remove(row[0].ToString());
            try
            {
                adapter.Fill(itemKeyword, row[0].ToString());
            }
            catch
            {
                return false;
            }
            adapter.SelectCommand.CommandText = "select item.ID,brandKeyword.keywordSN,brandKeyword.keyword from item inner join brand on item.brandID=brand.ID inner join brandKeyword on brand.ID=brandKeyword.brandID where brand.ID=@brandID";
            try
            {
                adapter.Fill(itemKeyword, row[0].ToString());
            }
            catch
            {
                return false;
            }
            return true;
        }
        #endregion
    }
}